package com.paic.mhis.hcpms.trust.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Name;

import com.paic.mhis.hcpms.trust.dto.BlackExcelDataSourceDTO;
import com.paic.pafa.exceptions.BusinessException;

/**
 * 
 * @author EX-CAOTIANHUA001
 * 
 */
public class ExportBlackComboxExcel {

	List<BlackExcelDataSourceDTO> drs = new ArrayList<BlackExcelDataSourceDTO>();

	/**
	 * 创建隐藏页和数据域
	 * 
	 * @param workbook
	 * @param hideSheetName
	 */
	public void creatHideSheet(HSSFWorkbook workbook, String hideSheetName, List<BlackExcelDataSourceDTO> dr) {
		HSSFSheet hideselectinfosheet = workbook.createSheet(hideSheetName);// 隐藏一些信息
		// 1.查询所有的系统名称，作为Excel的名称管理
		List<String> alias = new ArrayList<String>();
		if (dr != null) {
			for (BlackExcelDataSourceDTO sys : dr) {
				alias.add(sys.getLevelAlias());
			}
		}

		if (alias != null && alias.size() > 0) {
			// 系统，在隐藏页设置选择信息
			HSSFRow provinceRow = hideselectinfosheet.createRow(0);
			creatRow(provinceRow, alias);
			// 子系统，在隐藏页设置选择信息
			for (int i = 0; i < dr.size(); i++) {
				List<String> ls = dr.get(i).getDetailList();
				ls.add(0, alias.get(i));
				HSSFRow zjProvinceRow = hideselectinfosheet.createRow(i + 1);
				creatRow(zjProvinceRow, ls);
			}
		}

		// 设置隐藏页标志
		workbook.setSheetHidden(workbook.getSheetIndex(hideSheetName), true);
	}

	/** 获取所有信息系统数据 **/
	public static List<BlackExcelDataSourceDTO> getDataResource() {
		String[] a = { "A", "B", "C_", "D", "E" };
		Map<String, String> mp = new HashMap<String, String>();
		mp.put("A", "直接涉及倒卖药品案");
		mp.put("B", "涉嫌参与卖药或存在严重的医保违规行为或费用明显异常且数额较大（一个医保年度内统筹+个账支付>5万元）。");
		mp.put("C_", "存在明显的医保违规行为或费用明显异常且数额较大（一个医保年度内统筹+个账支付>3万元）。");
		mp.put("D", "存在医保违规行为或费用明显异常且数额较大（一个医保年度内统筹+个账支付>2万元）。");
		mp.put("E", "查实存在医保违规行为且给医保基金造成一定的损失（金额<0.5万元）。");

		List<BlackExcelDataSourceDTO> lit = new ArrayList<BlackExcelDataSourceDTO>();
		for (String str : a) {
			BlackExcelDataSourceDTO dr = new BlackExcelDataSourceDTO();
			dr.setLevelAlias(str);
			List<String> detailList = new ArrayList<String>();
			for (int i = 0; i < 5; i++) {
				detailList.add(mp.get(str).toString() + "_" + str + str + i);
			}
			dr.setDetailList(detailList);
			lit.add(dr);
		}
		return lit;
	}

	/**
	 * 创建一列数据
	 * 
	 * @param currentRow
	 * @param textList
	 */
	public void creatRow(HSSFRow currentRow, List<String> textList) {
		if (textList != null && textList.size() > 0) {
			int i = 0;
			for (String cellValue : textList) {
				HSSFCell userNameLableCell = currentRow.createCell(i++);
				userNameLableCell.setCellValue(cellValue);
			}
		}
	}

	/**
	 * 名称管理
	 * 
	 * @param workbook
	 * @throws FrameworkException
	 */
	public void creatExcelNameList(HSSFWorkbook workbook, List<BlackExcelDataSourceDTO> dr) {
		if (dr != null && dr.size() > 0) {
			// 名称管理
			Name name;
			name = workbook.createName();
			name.setSheetIndex(0);
			name.setNameName("sysytemInfo");

			name.setRefersToFormula("hideselectinfosheet!$A$1:$" + getcellColumnFlag(dr.size()) + "$1");

			List<String> alias = new ArrayList<String>();
			if (dr != null) {
				for (BlackExcelDataSourceDTO sys : dr) {
					alias.add(sys.getLevelAlias());
				}
			}

			if (alias != null && alias.size() > 0) {
				// 子系统，在隐藏页设置选择信息
				for (int i = 0; i < dr.size(); i++) {

					name = workbook.createName();
					// 父级系统
					name.setSheetIndex(0);
					name.setNameName(alias.get(i));
					// 子系统的范围
					name.setRefersToFormula("hideselectinfosheet!$B$" + (i + 2) + ":$"
							+ getcellColumnFlag(dr.get(i).getDetailList().size()) + "$" + (i + 2));
				}
			}
		}
	}

	// 根据数据值确定单元格位置（比如：28-AB）
	private String getcellColumnFlag(int num) {
		String columFiled = "";
		int chuNum = 0;
		int yuNum = 0;
		if (num >= 1 && num <= 26) {
			columFiled = this.doHandle(num);
		} else {
			chuNum = num / 26;
			yuNum = num % 26;

			columFiled += this.doHandle(chuNum);
			columFiled += this.doHandle(yuNum);
		}
		return columFiled;
	}

	private String doHandle(final int num) {
		String[] charArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
				"S", "T", "U", "V", "W", "X", "Y", "Z" };
		return charArr[num - 1].toString();
	}

	/**
	 * 生成导出下拉框excel
	 * 
	 * @param outPathStr
	 *            输出路径
	 */
	public ExportBlackComboxExcel(String outPathStr, HSSFWorkbook workbook, List<BlackExcelDataSourceDTO> data)
			throws BusinessException {
		try {
			for (int i = 0; i < workbook.getNumberOfSheets() - 1; i++) {
				if (i > 1) {
					workbook.removeName(i);
				}
			}
			creatHideSheet(workbook, "hideselectinfosheet", data);
			// 设置名称数据集
			creatExcelNameList(workbook, data);
			// 生成输入文件
			FileOutputStream out = new FileOutputStream(outPathStr);

			workbook.write(out);
			out.close();

			System.out.println("导出成功!");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new BusinessException(e.getCause().getMessage());
		} catch (IOException e) {
			e.printStackTrace();
			throw new BusinessException(e.getCause().getMessage());
		}
	}

	/**
	 * 生成导出下拉框excel
	 * 
	 * @param outPathStr
	 *            输出路径
	 * @throws BusinessException
	 */
	public ExportBlackComboxExcel(String filePath, String fileNameResource, String fileNameTarget,
			List<BlackExcelDataSourceDTO> data) throws BusinessException {
		File file = new File(filePath + fileNameResource);
		InputStream is;
		try {
			is = new FileInputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook(is);
			int size = workbook.getNumberOfSheets();
			for (int i = 0; i < size; i++) {
				if (workbook.getSheetName(i).equals("hideselectinfosheet")) {
					workbook.removeSheetAt(i);
				}
			}
			creatHideSheet(workbook, "hideselectinfosheet", data);
			// 设置名称数据集
			creatExcelNameList(workbook, data);
			// 生成输入文件
			FileOutputStream out = new FileOutputStream(filePath + fileNameTarget);

			workbook.write(out);
			out.close();

			System.out.println("导出成功!");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new BusinessException(e.getCause().getMessage());
		} catch (IOException e) {
			e.printStackTrace();
			throw new BusinessException(e.getCause().getMessage());
		}
	}

	public static void main(String[] args) {
		String filePath = "D:/Users/ex-caotianhua001/Documents/blackTemplate.xls";
		File file = new File(filePath);
		InputStream is;
		try {
			is = new FileInputStream(file);
			HSSFWorkbook wb = new HSSFWorkbook(is);

			new ExportBlackComboxExcel("d:\\ok3.xls", wb, getDataResource());

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (BusinessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}